package com.cmeduSystem.common.online.util;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.text.StrFormatter;
import cn.hutool.core.util.StrUtil;
import cn.hutool.http.HttpUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.cmeduSystem.common.core.constant.AggregationType;
import com.cmeduSystem.common.core.constant.DictType;
import com.cmeduSystem.common.core.constant.ObjectFieldType;
import com.cmeduSystem.common.core.exception.MyRuntimeException;
import com.cmeduSystem.common.core.object.ResponseResult;
import com.cmeduSystem.common.core.object.TokenData;
import com.cmeduSystem.common.core.object.Tuple2;
import com.cmeduSystem.common.dbutil.constant.DblinkType;
import com.cmeduSystem.common.dbutil.object.SqlTableColumn;
import com.cmeduSystem.common.dict.service.GlobalDictService;
import com.cmeduSystem.common.online.exception.OnlineRuntimeException;
import com.cmeduSystem.common.online.model.constant.RelationType;
import com.cmeduSystem.common.online.object.ConstDictInfo;
import lombok.Data;
import lombok.ToString;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.Serializable;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 在线表单自定义API操作的通用帮助对象。
 *
 * @author xulei
 * @date 2025-09-04
 */
@Slf4j
@Component
public class OnlineApiOperationHelper {

    @Autowired
    private GlobalDictService globalDictService;
    @Autowired
    private OnlineDataSourceUtil dataSourceUtil;

    private static final String DICT_MAP_SUFFIX = "DictMap";
    private static final String DICT_MAP_LIST_SUFFIX = "DictMapList";
    private static final String SELECT = "SELECT ";
    private static final String FROM = " FROM ";
    private static final String WHERE = " WHERE ";
    private static final String AND = " AND ";
    private static final String GROUP_BY = " GROUP BY ";

    /**
     * 聚合返回数据中，聚合键的常量字段名。
     * 如select groupColumn grouped_key, max(aggregationColumn) aggregated_value。
     */
    private static final String KEY_NAME = "grouped_key";
    /**
     * 聚合返回数据中，聚合值的常量字段名。
     * 如select groupColumn grouped_key, max(aggregationColumn) aggregated_value。
     */
    private static final String VALUE_NAME = "aggregated_value";

    /**
     * 绑定数据关联。
     *
     * @param dataList         主表数据列表。
     * @param relationDataList 关联数据列表。
     */
    public void buildRelation(List<Map<String, Object>> dataList, List<RelationData> relationDataList) {
        if (CollUtil.isEmpty(dataList)) {
            return;
        }
        for (RelationData relation : relationDataList) {
            Set<Object> masterIdSet = dataList.stream()
                    .map(data -> data.get(relation.masterIdColumn))
                    .filter(Objects::nonNull)
                    .collect(Collectors.toSet());
            if (CollUtil.isEmpty(masterIdSet)) {
                continue;
            }
            try {
                StringBuilder sql = new StringBuilder(256);
                sql.append(SELECT).append(" * ").append(FROM).append(relation.slaveTable)
                        .append(WHERE).append(this.makeSlaveDataInFilterList(relation.slaveIdColumn, masterIdSet));
                List<Map<String, Object>> slaveDataList =
                        dataSourceUtil.query(relation.dblinkId, sql.toString(), new LinkedList<>(masterIdSet));
                String relationColumn = relation.getMasterIdColumn() + "_" + relation.getSlaveTable();
                Map<Object, List<Map<String, Object>>> slaveDataMap = slaveDataList.stream().collect(
                        Collectors.groupingBy(data -> data.get(relation.slaveIdColumn).toString()));
                dataList.stream().filter(Objects::nonNull).forEach(
                        data -> bindRelationData(data, slaveDataMap, relationColumn, relation));
            } catch (Exception e) {
                String errorMsg = "Failed to build relation for dataList + [" + relation.toString() + "].";
                log.error(errorMsg, e);
                throw new MyRuntimeException(e);
            }
        }
    }

    /**
     * 绑定数据关联的聚合计算。
     *
     * @param dataList         主表数据列表。
     * @param relationDataList 关联数据列表。
     */
    public void buildRelationAggregation(List<Map<String, Object>> dataList, List<RelationData> relationDataList) {
        // 处理多一多场景下，根据主表的结果，进行从表聚合数据的计算。
        if (CollUtil.isEmpty(dataList)) {
            return;
        }
        for (RelationData relation : relationDataList) {
            Set<Object> masterIdSet = dataList.stream()
                    .map(data -> data.get(relation.masterIdColumn))
                    .filter(Objects::nonNull)
                    .collect(Collectors.toSet());
            if (CollUtil.isEmpty(masterIdSet)) {
                continue;
            }
            Tuple2<String, String> selectAndGroupByTuple = makeSelectListAndGroupByClause(relation);
            // 开始组装过滤从句。
            String criteriaString = this.makeSlaveDataInFilterList(relation.slaveIdColumn, masterIdSet);
            if (StrUtil.isNotBlank(relation.getFilterSql())) {
                criteriaString = criteriaString + AND + relation.getFilterSql();
            }
            StringBuilder sql = new StringBuilder(512);
            sql.append(SELECT)
                    .append(selectAndGroupByTuple.getFirst())
                    .append(FROM)
                    .append(relation.slaveTable)
                    .append(WHERE)
                    .append(criteriaString)
                    .append(GROUP_BY)
                    .append(selectAndGroupByTuple.getSecond());
            try {
                List<Map<String, Object>> aggregationDataList =
                        dataSourceUtil.query(relation.dblinkId, sql.toString(), new LinkedList<>(masterIdSet));
                if (CollUtil.isNotEmpty(aggregationDataList)) {
                    this.bindRelationAggregationData(dataList, aggregationDataList, relation);
                }
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                throw new OnlineRuntimeException(e.getMessage());
            }
        }
    }

    /**
     * 为数据集绑定字典关联。
     *
     * @param dataList         数据集。
     * @param relationDataList 字典关联数据列表。
     */
    public void buildDictRelation(List<Map<String, Object>> dataList, List<RelationDictData> relationDataList) {
        for (RelationDictData relationData : relationDataList) {
            switch (relationData.getDictType()) {
                case DictType.TABLE:
                    this.buildTableDictRelation(dataList, relationData);
                    break;
                case DictType.GLOBAL_DICT:
                    this.buildGlobalDictRelation(dataList, relationData);
                    break;
                case DictType.CUSTOM:
                    this.buildCustomDictRelation(dataList, relationData);
                    break;
                case DictType.URL:
                    this.buildUrlDictRelation(dataList, relationData);
                    break;
                default:
                    break;
            }
        }
    }

    /**
     * 为数据集绑定数据表字典的关联。
     *
     * @param dataList 数据集。
     * @param relation 字典关联数据。
     */
    public void buildTableDictRelation(List<Map<String, Object>> dataList, RelationDictData relation) {
        if (CollUtil.isEmpty(dataList)) {
            return;
        }
        Set<Object> masterIdSet = dataList.stream()
                .map(data -> data.get(relation.dataIdColumn))
                .filter(Objects::nonNull)
                .collect(Collectors.toSet());
        if (CollUtil.isEmpty(masterIdSet)) {
            return;
        }
        List<Object> normalizedIds;
        try {
            if (relation.multiSelect) {
                List<String> normalizedMasterIdSet = masterIdSet.stream()
                        .map(Object::toString)
                        .filter(StrUtil::isNotBlank)
                        .collect(Collectors.toList());
                String allIds = CollUtil.join(normalizedMasterIdSet, StrUtil.COMMA);
                normalizedIds = this.convertByJavaType(
                        relation.dictDblinkId, relation.dictTable, relation.dictIdColumn, StrUtil.split(allIds, StrUtil.COMMA));
            } else {
                normalizedIds = new LinkedList<>(masterIdSet);
            }
            StringBuilder sql = new StringBuilder(256);
            sql.append(SELECT)
                    .append(relation.dictIdColumn)
                    .append(StrUtil.COMMA)
                    .append(relation.dictNameColumn)
                    .append(FROM)
                    .append(relation.dictTable)
                    .append(WHERE)
                    .append(this.makeSlaveDataInFilterList(relation.dictIdColumn, normalizedIds));
            List<Map<String, Object>> dictDataList =
                    dataSourceUtil.query(relation.dictDblinkId, sql.toString(), normalizedIds);
            Map<String, String> dictMap = new HashMap<>(20);
            dictDataList.forEach(dictData -> {
                Object dictId = dictData.get(relation.dictIdColumn);
                if (dictId != null) {
                    dictMap.put(dictId.toString(), dictData.get(relation.dictNameColumn).toString());
                }
            });
            String relationColumn = this.makeDictRelationColumnName(relation.getDataIdColumn(), relation.multiSelect);
            dataList.stream().filter(Objects::nonNull).forEach(
                    data -> bindDictRelationData(data, dictMap, relationColumn, relation));
        } catch (Exception e) {
            String errorMsg = "Failed to build dict for dataList [" + relation.toString() + "].";
            log.error(errorMsg, e);
            throw new MyRuntimeException(e);
        }
    }

    /**
     * 为数据集绑定全局编码字典的关联。
     *
     * @param dataList 数据集。
     * @param relation 字典关联数据。
     */
    public void buildGlobalDictRelation(
            List<Map<String, Object>> dataList, RelationDictData relation) {
        if (CollUtil.isEmpty(dataList)) {
            return;
        }
        Set<Serializable> masterIdSet = dataList.stream()
                .map(data -> data.get(relation.dataIdColumn))
                .filter(Objects::nonNull)
                .map(Object::toString)
                .collect(Collectors.toSet());
        if (CollUtil.isEmpty(masterIdSet)) {
            return;
        }
        Set<Serializable> normalizedIds = masterIdSet;
        if (relation.multiSelect) {
            List<String> normalizedMasterIdSet = masterIdSet.stream()
                    .map(Object::toString)
                    .filter(StrUtil::isNotBlank)
                    .collect(Collectors.toList());
            if (CollUtil.isNotEmpty(normalizedMasterIdSet)) {
                String allIds = CollUtil.join(normalizedMasterIdSet, StrUtil.COMMA);
                normalizedIds = new HashSet<>(StrUtil.split(allIds, StrUtil.COMMA));
            }
        }
        String relationColumn = this.makeDictRelationColumnName(relation.getDataIdColumn(), relation.multiSelect);
        Map<Serializable, String> dictMap = globalDictService.getGlobalDictItemDictMapFromCache(relation.dictCode, normalizedIds);
        dataList.stream().filter(Objects::nonNull).forEach(
                data -> bindDictRelationData(data, dictMap, relationColumn, relation));
    }

    /**
     * 为数据集绑定自定义字典的关联。
     *
     * @param dataList 数据集。
     * @param relation 字典关联数据。
     */
    public void buildCustomDictRelation(
            List<Map<String, Object>> dataList, RelationDictData relation) {
        if (CollUtil.isEmpty(dataList)) {
            return;
        }
        Set<Object> masterIdSet = dataList.stream()
                .map(data -> data.get(relation.dataIdColumn))
                .filter(Objects::nonNull)
                .collect(Collectors.toSet());
        if (CollUtil.isEmpty(masterIdSet)) {
            return;
        }
        ConstDictInfo dictInfo = JSONObject.parseObject(relation.dictCustomData, ConstDictInfo.class);
        List<ConstDictInfo.ConstDictData> dictDataList = dictInfo.getDictData();
        Map<String, String> dictMap = new HashMap<>(dictDataList.size());
        for (ConstDictInfo.ConstDictData dictData : dictDataList) {
            dictMap.put(dictData.getId().toString(), dictData.getName());
        }
        String relationColumn = this.makeDictRelationColumnName(relation.getDataIdColumn(), relation.multiSelect);
        dataList.stream().filter(Objects::nonNull).forEach(
                data -> bindDictRelationData(data, dictMap, relationColumn, relation));
    }

    /**
     * 为数据集绑定URL字典的关联。
     *
     * @param dataList 数据集。
     * @param relation 字典关联数据。
     */
    public void buildUrlDictRelation(List<Map<String, Object>> dataList, RelationDictData relation) {
        if (CollUtil.isEmpty(dataList)) {
            return;
        }
        Set<Object> masterIdSet = dataList.stream()
                .map(data -> data.get(relation.dataIdColumn))
                .filter(Objects::nonNull)
                .collect(Collectors.toSet());
        if (CollUtil.isEmpty(masterIdSet)) {
            return;
        }
        Map<String, Object> param = new HashMap<>(1);
        if (TokenData.takeFromRequest() != null) {
            param.put("Authorization", TokenData.takeFromRequest().getToken());
        }
        String responseData = HttpUtil.get(relation.dictListUrl, param);
        ResponseResult<JSONArray> responseResult =
                JSON.parseObject(responseData, new TypeReference<ResponseResult<JSONArray>>() {
                });
        if (!responseResult.isSuccess()) {
            throw new OnlineRuntimeException(responseResult.getErrorMessage());
        }
        JSONArray dictDataArray = responseResult.getData();
        Map<String, String> dictMap = new HashMap<>(16);
        for (int i = 0; i < dictDataArray.size(); i++) {
            JSONObject dictData = dictDataArray.getJSONObject(i);
            dictMap.put(dictData.getString(relation.dictIdColumn), dictData.getString(relation.dictNameColumn));
        }
        String relationColumn = this.makeDictRelationColumnName(relation.getDataIdColumn(), relation.multiSelect);
        dataList.stream().filter(Objects::nonNull).forEach(
                data -> bindDictRelationData(data, dictMap, relationColumn, relation));
    }

    /**
     * 根据Java类型将数据转换为目标类型的数据列表。
     *
     * @param ids 字符串类型的数据列表。
     * @return 转换后目标类型的数据列表。
     */
    public List<Object> convertByJavaType(Long dblinkId, String tableName, String columnName, List<String> ids) {
        int dblinkType = dataSourceUtil.getDblinkTypeByDblinkId(dblinkId);
        SqlTableColumn column = dataSourceUtil.getTableColum(dblinkId, tableName, columnName);
        String javaType = dataSourceUtil.convertToJavaType(column, dblinkType);
        switch (javaType) {
            case ObjectFieldType.STRING:
                return new LinkedList<>(ids);
            case ObjectFieldType.INTEGER:
                return ids.stream().map(Integer::valueOf).collect(Collectors.toList());
            case ObjectFieldType.LONG:
            case ObjectFieldType.BIG_DECIMAL:
                return ids.stream().map(Long::valueOf).collect(Collectors.toList());
            default:
                throw new MyRuntimeException("Unsupported ColumnType [" + javaType + "].");
        }
    }

    private static void bindRelationData(
            Map<String, Object> masterData,
            Map<Object, List<Map<String, Object>>> slaveDataMap,
            String relationColumn,
            RelationData relation) {
        Object id = masterData.get(relation.masterIdColumn);
        if (id == null) {
            return;
        }
        List<Map<String, Object>> slaveData = slaveDataMap.get(id.toString());
        if (CollUtil.isEmpty(slaveData)) {
            return;
        }
        if (relation.getRelationType().equals(RelationType.ONE_TO_MANY)) {
            masterData.put(relationColumn, slaveData);
        } else if (relation.getRelationType().equals(RelationType.ONE_TO_ONE)) {
            masterData.put(relationColumn, slaveData.get(0));
        }
    }

    private void bindRelationAggregationData(
            List<Map<String, Object>> dataList, List<Map<String, Object>> aggregationDataList, RelationData relation) {
        Map<String, Object> relatedMap = new HashMap<>(aggregationDataList.size());
        for (Map<String, Object> map : aggregationDataList) {
            relatedMap.put(map.get(KEY_NAME).toString(), map.get(VALUE_NAME));
        }
        for (Map<String, Object> dataObject : dataList) {
            String masterIdValue = dataObject.get(relation.getMasterIdColumn()).toString();
            if (masterIdValue != null) {
                Object value = relatedMap.get(masterIdValue);
                if (value != null) {
                    dataObject.put(relation.resultColumn, value);
                }
            }
        }
    }

    private static void bindDictRelationData(
            Map<String, Object> masterData,
            Map<? extends Serializable, String> dictMap,
            String dictRelationColumn,
            RelationDictData relation) {
        Object id = masterData.get(relation.dataIdColumn);
        if (id == null) {
            return;
        }
        if (relation.multiSelect) {
            List<String> dictIdSet = StrUtil.split(id.toString(), StrUtil.COMMA);
            List<Map<String, Object>> mapList = new LinkedList<>();
            for (String dictId : dictIdSet) {
                String name = dictMap.get(dictId);
                if (name != null) {
                    Map<String, Object> m = new HashMap<>(2);
                    m.put("id", dictId);
                    m.put("name", name);
                    mapList.add(m);
                }
            }
            masterData.put(dictRelationColumn, mapList);
        } else {
            String name = dictMap.get(id.toString());
            if (name != null) {
                Map<String, Object> m = new HashMap<>(2);
                m.put("id", id);
                m.put("name", name);
                masterData.put(dictRelationColumn, m);
            }
        }
    }

    private String makeSlaveDataInFilterList(String columnName, Collection<Object> filterIdSet) {
        return columnName + " IN (" + StrUtil.repeatAndJoin("?", filterIdSet.size(), StrUtil.COMMA) + ")";
    }

    private String makeDictRelationColumnName(String dataIdColumn, boolean multiSelect) {
        return dataIdColumn + (multiSelect ? DICT_MAP_LIST_SUFFIX : DICT_MAP_SUFFIX);
    }

    private Tuple2<String, String> makeSelectListAndGroupByClause(RelationData relation) {
        if (!AggregationType.isValid(relation.aggregationType)) {
            throw new IllegalArgumentException("Invalid AggregationType Value [" + relation.aggregationType + "].");
        }
        StringBuilder groupedSelectList = new StringBuilder(128);
        if (StrUtil.isBlank(relation.aggregationColumn)) {
            throw new IllegalArgumentException("AggregationColumn CAN'T be EMPTY.");
        }
        if (StrUtil.isBlank(relation.resultColumn)) {
            throw new IllegalArgumentException("resultColumn CAN'T be EMPTY.");
        }
        if (relation.aggregationType.equals(AggregationType.CONCAT)) {
            String aggrClause = this.makeConcatAggregationColumn(relation);
            groupedSelectList.append(relation.slaveTable)
                    .append(".")
                    .append(relation.slaveIdColumn)
                    .append(" ")
                    .append(KEY_NAME)
                    .append(", ")
                    .append(aggrClause)
                    .append(VALUE_NAME)
                    .append(" ");
        } else {
            String aggregationFunc = AggregationType.getAggregationFunction(relation.aggregationType);
            // 构建Select List
            // 如：r_table.master_id groupedKey, SUM(r_table.aggr_column) aggregated_value
            groupedSelectList.append(relation.slaveTable)
                    .append(".")
                    .append(relation.slaveIdColumn)
                    .append(" ")
                    .append(KEY_NAME)
                    .append(", ")
                    .append(aggregationFunc)
                    .append("(")
                    .append(relation.slaveTable)
                    .append(".")
                    .append(relation.aggregationColumn)
                    .append(") ")
                    .append(VALUE_NAME)
                    .append(" ");
        }
        StringBuilder groupBy = new StringBuilder(64);
        groupBy.append(relation.slaveTable).append(".").append(relation.slaveIdColumn);
        return new Tuple2<>(groupedSelectList.toString(), groupBy.toString());
    }

    private String makeConcatAggregationColumn(RelationData relation) {
        int dblinkType = dataSourceUtil.getDblinkTypeByDblinkId(relation.dblinkId);
        StringBuilder groupedSelectList = new StringBuilder(128);
        if (dblinkType == DblinkType.MYSQL || dblinkType == DblinkType.KINGBASE) {
            groupedSelectList
                    .append("GROUP_CONCAT(")
                    .append(relation.slaveTable)
                    .append(".")
                    .append(relation.aggregationColumn)
                    .append(") ");
        } else if (dblinkType == DblinkType.POSTGRESQL || dblinkType == DblinkType.OPENGAUSS) {
            groupedSelectList
                    .append("STRING_AGG(")
                    .append(relation.slaveTable)
                    .append(".")
                    .append(relation.aggregationColumn)
                    .append(",',') ");
        } else if (dblinkType == DblinkType.ORACLE || dblinkType == DblinkType.DAMENG) {
            groupedSelectList
                    .append("LISTAGG(")
                    .append(relation.slaveTable)
                    .append(".")
                    .append(relation.aggregationColumn)
                    .append(",',')  WITHIN GROUP (ORDER BY ")
                    .append(relation.slaveTable)
                    .append(".")
                    .append(relation.aggregationColumn)
                    .append(") ");
        } else if (dblinkType == DblinkType.SQLSERVER) {
            String s = "STUFF((SELECT ',' + {} FROM {} t WHERE {} = {}.{} FOR XML PATH('')),1, 1, '')";
            groupedSelectList.append(StrFormatter.format(s, relation.aggregationColumn,
                    relation.slaveTable, relation.slaveIdColumn, relation.slaveTable, relation.slaveIdColumn));
        }
        return groupedSelectList.toString();
    }

    @Data
    @ToString
    public static class RelationData {
        /**
         * 关联类型，具体值参考RelationType常量类。
         */
        private Integer relationType;
        /**
         * 聚合类型，具体值参考AggregationType常量类。
         */
        private Integer aggregationType;
        /**
         * 主表关联字段名。
         */
        private String masterIdColumn;
        /**
         * 数据库链接Id。主从表位于同一数据库。
         */
        private Long dblinkId;
        /**
         * 从表表名。
         */
        private String slaveTable;
        /**
         * 从表关联字段名。
         */
        private String slaveIdColumn;
        /**
         * 从表中参与聚合计算的字段名。
         */
        private String aggregationColumn;
        /**
         * 聚合计算结果的字段名。
         */
        private String resultColumn;
        /**
         * 过滤条件。
         */
        private String filterSql;
    }

    @Data
    @ToString
    public static class RelationDictData {
        /**
         * 字典类型，具体值参考DictType常量类。
         */
        private Integer dictType;
        /**
         * 数据集字典字段名。
         */
        private String dataIdColumn;
        /**
         * 数据库连接ID。仅数据表字典可用。
         */
        private Long dictDblinkId;
        /**
         * 字典表名。仅数据表字典可用。
         */
        private String dictTable;
        /**
         * 字典ID字段名。仅数据表和URL字典可用。
         */
        private String dictIdColumn;
        /**
         * 字典名字段名。仅数据表和URL字典可用。
         */
        private String dictNameColumn;
        /**
         * 字典编码，仅全局编码字典可用。
         */
        private String dictCode;
        /**
         * 字典数据。仅自定义字典可用。
         */
        private String dictCustomData;
        /**
         * 字典数据请求地址。仅URL字典可用。
         */
        private String dictListUrl;
        /**
         * 过滤条件。仅用于数据表字典。
         */
        private String filterSql;
        /**
         * 是否多选。
         */
        private boolean multiSelect;
    }
}
